﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;


namespace syc.Produce
{
    public partial class 分厂超产增值月报 : System.Web.UI.Page
    {
        protected HSSFWorkbook hssfworkbook;
        protected double[] JiHua = new double[7];
        protected double[] WanCheng = new double[7];
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                this.NianDu.Text = DateTime.Today.Year.ToString();
                this.YueFen.Text = DateTime.Today.Month.ToString();
            }
            QueryData();
        }
        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("模板/分厂超产增值月报.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "陕西延长石油材料有限公司";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "分厂超产增值月报";
            hssfworkbook.SummaryInformation = si;
        }

        protected void WriteToFile()
        {
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + Server.UrlEncode(this.YueFen.SelectedItem.Text+"分厂超产增值月报")+".xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }

        private void QueryData()
        {
            int i;
            for (i = 0; i < 7; i++)
            {
                JiHua[i] = 0;
                WanCheng[i] = 0;
            }
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText = "select * from 生产计划 where 年度=" + this.NianDu.Text + " and 月份=" + this.YueFen.Text;
            SqlDataReader Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                JiHua[0] = (double)Reader["一分厂产品2"];
                JiHua[1] = (double)Reader["二分厂产品2"];
                JiHua[2] = (double)Reader["二分厂产品1"];
                JiHua[3] = (double)Reader["三分厂产品2"];
                JiHua[4] = (double)Reader["三分厂产品1"];
                JiHua[5] = (double)Reader["四分厂产品2"];
                JiHua[6] = (double)Reader["五分厂产品1"];
            }
            Reader.Close();

            DateTime d1, d2, d3;
            string sql=
            @"declare @d1 smalldatetime
declare @d2 smalldatetime
if exists(select * from 生产月设置 where 分厂=@fc and 年度=@y and 月份=@m)
begin
	select @d1=起始日期,@d2=截止日期 from 生产月设置 where 分厂=@fc and 年度=@y and 月份=@m
	if exists(select * from 生产月设置 where 分厂=@fc and 年度=@y and 月份=1)
	begin
		select @d1 as 月初,@d2 as 月末,起始日期 as 年初 from 生产月设置 where 分厂=@fc and 年度=@y and 月份=1
	end
end";
            //统计一分厂完成量
            Cmd.CommandText = sql.Replace("@y", this.NianDu.Text);
            Cmd.CommandText = Cmd.CommandText.Replace("@m", this.YueFen.Text);
            Cmd.CommandText = Cmd.CommandText.Replace("@fc", "1");
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                d1 = (DateTime)Reader["月初"];
                d2 = (DateTime)Reader["月末"];
                d3 = (DateTime)Reader["年初"];
            }
            else
            {
                d1 = DateTime.Parse(this.NianDu.Text + "年" + this.YueFen.Text + "月" + "1日");
                d2 = d1.AddMonths(1).AddDays(-1);
                d3 = DateTime.Parse(this.NianDu.Text + "年1月1日");
            }
            Reader.Close();

            Cmd.CommandText = "select sum(cast(入库重量 as float)) from 生产进度 where 分厂ID=1 and 日期>='" + d1.ToShortDateString() + "' and 日期<='" + d2.ToShortDateString() + "' and 产品类别ID=2";
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                if (!Reader.IsDBNull(0))
                {
                    WanCheng[0] = (double)Reader[0];
                }
            }
            Reader.Close();

            //统计二分厂完成量
            Cmd.CommandText = sql.Replace("@y", this.NianDu.Text);
            Cmd.CommandText = Cmd.CommandText.Replace("@m", this.YueFen.Text);
            Cmd.CommandText = Cmd.CommandText.Replace("@fc", "2");
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                d1 = (DateTime)Reader["月初"];
                d2 = (DateTime)Reader["月末"];
                d3 = (DateTime)Reader["年初"];
            }
            else
            {
                d1 = DateTime.Parse(this.NianDu.Text + "年" + this.YueFen.Text + "月" + "1日");
                d2 = d1.AddMonths(1).AddDays(-1);
                d3 = DateTime.Parse(this.NianDu.Text + "年1月1日");
            }
            Reader.Close();

            Cmd.CommandText = "select sum(case when 产品类别ID=1 then cast(入库重量 as float) else 0 end) as 油管入库量,sum(case when 产品类别ID=2 then cast(入库重量 as float) else 0 end) as 套管入库量 from 生产进度 where 分厂ID=2 and 日期>='" + d1.ToShortDateString() + "' and 日期<='" + d2.ToShortDateString() + "'";
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                if (!Reader.IsDBNull(0))
                {
                    WanCheng[1] = (double)Reader[1];
                    WanCheng[2] = (double)Reader[0];
                }
            }
            Reader.Close();


            //统计三分厂完成量
            Cmd.CommandText = sql.Replace("@y", this.NianDu.Text);
            Cmd.CommandText = Cmd.CommandText.Replace("@m", this.YueFen.Text);
            Cmd.CommandText = Cmd.CommandText.Replace("@fc", "3");
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                d1 = (DateTime)Reader["月初"];
                d2 = (DateTime)Reader["月末"];
                d3 = (DateTime)Reader["年初"];
            }
            else
            {
                d1 = DateTime.Parse(this.NianDu.Text + "年" + this.YueFen.Text + "月" + "1日");
                d2 = d1.AddMonths(1).AddDays(-1);
                d3 = DateTime.Parse(this.NianDu.Text + "年1月1日");
            }
            Reader.Close();

            Cmd.CommandText = "select sum(case when 产品类别ID=1 then 磷化支数 else 0 end) as 油箍,sum(case when 产品类别ID=2 then 磷化支数 else 0 end) as 套箍 from 生产进度3 where 日期>='" + d1.ToShortDateString() + "' and 日期<='" + d2.ToShortDateString() + "'";
            //Response.Write(Cmd.CommandText);
            //Response.End();
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                if (!Reader.IsDBNull(0))
                {
                    WanCheng[3] = (int)Reader[1];
                    WanCheng[4] = (int)Reader[0];
                }
            }
            Reader.Close();

            //统计四分厂完成量
            Cmd.CommandText = sql.Replace("@y", this.NianDu.Text);
            Cmd.CommandText = Cmd.CommandText.Replace("@m", this.YueFen.Text);
            Cmd.CommandText = Cmd.CommandText.Replace("@fc", "4");
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                d1 = (DateTime)Reader["月初"];
                d2 = (DateTime)Reader["月末"];
                d3 = (DateTime)Reader["年初"];
            }
            else
            {
                d1 = DateTime.Parse(this.NianDu.Text + "年" + this.YueFen.Text + "月" + "1日");
                d2 = d1.AddMonths(1).AddDays(-1);
                d3 = DateTime.Parse(this.NianDu.Text + "年1月1日");
            }
            Reader.Close();

            Cmd.CommandText = "select sum(入库) from 生产进度4 where 日期>='" + d1.ToShortDateString() + "' and 日期<='" + d2.ToShortDateString() + "' and 产品类别ID=2";
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                if (!Reader.IsDBNull(0))
                {
                    WanCheng[5] = (int)Reader[0];
                }
            }
            Reader.Close();


            //统计五分厂完成量
            Cmd.CommandText = sql.Replace("@y", this.NianDu.Text);
            Cmd.CommandText = Cmd.CommandText.Replace("@m", this.YueFen.Text);
            Cmd.CommandText = Cmd.CommandText.Replace("@fc", "5");
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                d1 = (DateTime)Reader["月初"];
                d2 = (DateTime)Reader["月末"];
                d3 = (DateTime)Reader["年初"];
            }
            else
            {
                d1 = DateTime.Parse(this.NianDu.Text + "年" + this.YueFen.Text + "月" + "1日");
                d2 = d1.AddMonths(1).AddDays(-1);
                d3 = DateTime.Parse(this.NianDu.Text + "年1月1日");
            }
            Reader.Close();

            Cmd.CommandText = "select sum(cast(入库重量 as float)) from 生产进度 where 分厂ID=5 and 日期>='" + d1.ToShortDateString() + "' and 日期<='" + d2.ToShortDateString() + "' and 产品类别ID=1";
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                if (!Reader.IsDBNull(0))
                {
                    WanCheng[6] = (double)Reader[0];
                }
            }
            Reader.Close();

            Reader.Dispose();
            Cmd.Dispose();
            Conn.Close();
            Conn.Dispose();
        }

        protected void Button_Export_Click(object sender, EventArgs e)
        {
            InitializeWorkbook();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("报表");

            sheet1.GetRow(0).GetCell(0).SetCellValue(this.YueFen.SelectedItem.Text+"月份各分厂超产增值月报");

            this.QueryData();

            int i;
            for (i = 0; i < 7; i++)
            {
                sheet1.GetRow(i+3).GetCell(3).SetCellValue(JiHua[i]);
                if (i == 3 || i == 4 || i == 5)
                {
                    sheet1.GetRow(i + 3).GetCell(4).SetCellValue(WanCheng[i]);
                }
                else
                {
                    sheet1.GetRow(i + 3).GetCell(4).SetCellValue((WanCheng[i]/1000).ToString("0.000"));
                }
            }
            sheet1.GetRow(3).GetCell(7).SetCellValue(this.DanJia2.Text);
            sheet1.GetRow(4).GetCell(7).SetCellValue(this.DanJia2.Text);
            sheet1.GetRow(5).GetCell(7).SetCellValue(this.DanJia1.Text);
            sheet1.GetRow(9).GetCell(7).SetCellValue(this.DanJia1.Text);

            //string op = (string)this.Session["OperatorName"];
            //if (op == null)
            //{
            //    op = "";
            //}
            //sheet1.GetRow(10).GetCell(5).SetCellValue("");
            sheet1.GetRow(10).GetCell(6).SetCellValue(DateTime.Today.ToLongDateString());

            sheet1.ForceFormulaRecalculation = true;
            WriteToFile();
            Response.End();
        }
    }
}
